﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.ComponentModel;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using INI;
using db;

namespace Testline
{
    
    public static class TestlineDB
    {
        public static MySqlConnection conn;
        public static string IP_ShopfloorWeb;
        public static string IP_ShopfloorDB;
        public static string User_ShopfloorDB;
        public static string Pass_ShopfloorDB;
        public static string Database_ShopfloorDB;
        public static INIClass INI_ShopfloorSetting = new INIClass(Application.StartupPath.TrimEnd('\\') + "\\Shopfloor.ini");
        public static INIClass INI_FieldName = new INIClass(Application.StartupPath.TrimEnd('\\') + "\\FieldName.ini");
        //public static INIClass INI_FieldSetting = new INIClass(Application.StartupPath.TrimEnd('\\') + "\\FieldSetting.ini");

        #region<------------init Operate-------->
        public static void Init()
        {
            ReadAllFromINI();
            WriteAllToINI();
            conn = new MySqlConnection(string.Format("server={0};uid={1};pwd={2};database={3}", IP_ShopfloorDB, User_ShopfloorDB, Pass_ShopfloorDB, Database_ShopfloorDB));
        }
        private static void ReadAllFromINI()
        {
            IP_ShopfloorWeb = INI_ShopfloorSetting.IniReadValue("Shopfloor", "IP_ShopfloorWeb", "127.0.0.1");
            IP_ShopfloorDB = INI_ShopfloorSetting.IniReadValue("Shopfloor", "IP_ShopfloorDB", "127.0.0.1");
            User_ShopfloorDB = INI_ShopfloorSetting.IniReadValue("Shopfloor", "User_ShopfloorDB", "root");
            Pass_ShopfloorDB = INI_ShopfloorSetting.IniReadValue("Shopfloor", "Pass_ShopfloorDB", "");
            Database_ShopfloorDB = INI_ShopfloorSetting.IniReadValue("Shopfloor", "Database_ShopfloorDB", "shopfloor");
        }
        public static void WriteAllToINI()
        {
            INI_ShopfloorSetting.IniWriteValue("Shopfloor", "IP_ShopfloorWeb", IP_ShopfloorWeb);
            INI_ShopfloorSetting.IniWriteValue("Shopfloor", "IP_ShopfloorDB", IP_ShopfloorDB);
            INI_ShopfloorSetting.IniWriteValue("Shopfloor", "User_ShopfloorDB", User_ShopfloorDB);
            INI_ShopfloorSetting.IniWriteValue("Shopfloor", "Pass_ShopfloorDB", Pass_ShopfloorDB);
            INI_ShopfloorSetting.IniWriteValue("Shopfloor", "Database_ShopfloorDB", Database_ShopfloorDB);
        }
        public static string GetFieldDescriptionFromINI(string TableName, string FieldName)
        {
            return INI_FieldName.IniReadValue(TableName, FieldName, FieldName);
        }

        #endregion

        #region <----------Functions----------->

        /// <summary>带有排序功能，条件查询某个表的内容，并且将该表的字段名称用FieldName.INI里面的设定进行替换
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Fields"></param>
        /// <param name="Where"></param>
        /// <param name="OrderBy"></param>
        /// <param name="IfDESC"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public static DataTable Query(string TableName, string Fields, string Where, string OrderBy, bool IfDESC, int limit)
        {
            string strOrder = IfDESC ? "DESC" : "ASC";
            string sql = string.Format("select {0} from {1} where {2} order by {3} {4} limit {5}", Fields, TableName, Where, OrderBy, strOrder, limit.ToString());
            if (conn.State != ConnectionState.Open) conn.Open();
            using (DataTable dt = new DataTable())
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                da.Fill(dt);
                //conn.Close();
                foreach (DataColumn col in dt.Columns)
                {
                    col.ColumnName = GetFieldDescriptionFromINI(TableName, col.ColumnName);
                }
                return dt;
            }
        }

        /// <summary>无排序功能的条件查询某个表的内容，并且将该表的字段名称用FieldName.INI里面的设定进行替换
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Fields"></param>
        /// <param name="Where"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public static DataTable Query(string TableName, string Fields, string Where, int limit)
        {
            string sql = string.Format("select {0} from {1} where {2} limit {3}", Fields, TableName, Where, limit.ToString());
            if (conn.State != ConnectionState.Open) conn.Open();
            using (DataTable dt = new DataTable())
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                da.Fill(dt);
                //conn.Close();
                foreach (DataColumn col in dt.Columns)
                {
                    col.ColumnName = GetFieldDescriptionFromINI(TableName, col.ColumnName);
                }
                return dt;
            }
        }

        /// <summary>无排序，无条件查询某个表的内容，并且将该表的字段名称用FieldName.INI里面的设定进行替换
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Fields"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public static DataTable Query(string TableName, string Fields, int limit)
        {
            string sql = string.Format("select {0} from {1}  limit {2}", Fields, TableName, limit.ToString());
            if (conn.State != ConnectionState.Open) conn.Open();
            using (DataTable dt = new DataTable())
            {
                MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                da.Fill(dt);
                //conn.Close();
                foreach (DataColumn col in dt.Columns)
                {
                    col.ColumnName = GetFieldDescriptionFromINI(TableName, col.ColumnName);
                }
                return dt;
            }
        }

        /// <summary>自定义MySQL查询功能，应用范围最广，可以适应所有的select语句
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public static DataTable Query(string SQL)
        {
            string sql = SQL;
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                using (DataTable dt = new DataTable())
                {
                    MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
                    da.Fill(dt);
                    //conn.Close();
                    return dt;
                }
            }
            catch (MySqlException msE)
            {
                MessageBox.Show(msE.Message);
                return null;
            }
        }

        /// <summary>查询一个时间段内有哪些Testplan被生产
        /// </summary>
        /// <param name="starttime">格式20130729</param>
        /// <param name="endtime">格式20130729</param>
        /// <param name="tablename">cr1T030tp,cr1T020tp,cr3line1tp.....</param>
        /// <returns></returns>
        public static DataTable GetTestPlansInPeriod(int starttime, int endtime, string tablename)
        {
            return GetTestPlansInPeriod(starttime.ToString(), endtime.ToString(), tablename);
        }

        #endregion


        internal static DataTable GetTestPlansInPeriod(string starttime, string endtime, string tablename)
        {
            string sSql;
            sSql = "SELECT type_id FROM " + tablename + " as cr1T040 where cr1T040.offload between " + starttime + "000000 and " + endtime + "000000 group by type_id";
            return dbFunction.Query(conn, sSql);
        }



    }

}
